/**
 * 
 */
package org.lookouster.mopg.bo;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Locale;

import org.lookouster.mopg.connector.Database;

/**
 * @author lkster
 *
 */
public class Bill {
	
	String billNumber;
	String customerNumber;
	String billingDate;
	String limitDate;
	ServiceProvider sp;
	Double billAmount;
	
	/**
	 * Constructs bill from a delimited text file line
	 * @param entry
	 * @param delim
	 * @param provider
	 */
	public Bill(String entry, String delim, int provider) 
	{

			String[] temp = entry.split(",");
			billNumber     = temp[0];
			customerNumber = temp[1];
			billingDate    = temp[2];
			limitDate      = temp[3];
			sp             = new ServiceProvider(provider);
			billAmount     = Double.parseDouble(temp[4]);						
	}
	
	public Bill(String no, String cust_no, String bDate, String lDate, ServiceProvider s, Double amount)
	{
		billNumber = no;
		customerNumber = cust_no;
		billingDate = bDate;
		limitDate = lDate;
		sp = s;
		billAmount = amount;
	}
	
	public void persistTemp()
	{
		String query = "INSERT INTO  bill_upload(bill_no, customer_no, billing_date, limit_date, fk_provider_id, Amount) VALUES('";
		query = query + billNumber + "' ,'" + customerNumber + "' ,'" + billingDate + "' ,'" + limitDate + "' ,'" + sp.getProviderId() + "' ," + billAmount + ")";
		Database.execute(query);
	}
	
	public static boolean importBills()
	{
		return Database.execute("insert into received_bills (bill_no, customer_no, billing_date, limit_date, fk_provider_id, Amount) SELECT  bu.bill_no, bu.customer_no, bu.billing_date, bu.limit_date, bu.fk_provider_id, bu.Amount FROM bill_upload bu left outer join received_bills rb using(bill_no) where bu.bill_no <> ' ' and rb.bill_no is NULL ");
	}
	
	public static void emptyTempTable()
	{
		Database.execute("TRUNCATE TABLE bill_upload");
	}
	
	public static Double getTotalTempAmount() throws SQLException
	{
		return Database.retrieveData("SELECT COUNT(*) AS 'Nb', SUM(Amount) as 'Total' from bill_upload ").getDouble("Total");
	}
	
	public static int getTempRowCount() throws SQLException
	{
		return Database.retrieveData("SELECT COUNT(*) AS 'Nb', SUM(Amount) as 'Total' from bill_upload ").getInt("Nb");
	}
	
	public static int getTempValidBillNoCount() throws SQLException
	{
		return Database.retrieveData("SELECT COUNT(*) as 'Nb', SUM(Amount) as 'Total' from bill_upload where bill_no <> ' '").getInt("Nb");
	}
	
	public static Double getTempTotalValidBillNoAmount() throws SQLException
	{
		return Database.retrieveData("SELECT COUNT(*) as 'Nb', SUM(Amount) as 'Total' from bill_upload where bill_no <> ' '").getDouble("Total");
	}
	
	public static int getTempEmptyBillNoCount() throws SQLException
	{
		return Database.retrieveData("SELECT COUNT(*) as 'Nb', SUM(Amount) as 'Total' from bill_upload where bill_no =  ' '").getInt("Nb");
	}
	
	public static Double getTempTotalEmptyBillNoAmount() throws SQLException
	{
		return Database.retrieveData("SELECT COUNT(*) as 'Nb', SUM(Amount) as 'Total' from bill_upload where bill_no =  ' '").getDouble("Total");
	}
	
	public static int getTempDuplicatesCount() throws SQLException
	{
		return Database.retrieveData("SELECT COUNT(*) as 'nb_dup' from (SELECT bill_no, COUNT(*) as 'Nb', SUM(Amount) as 'Total' from bill_upload group by bill_no having Nb > 1 and bill_no <> ' ') as t").getInt("nb_dup");
	}
	
	public static int getTempExistingBillNoCount() throws SQLException
	{
		return Database.retrieveData("SELECT COUNT(*) as 'nb_ex' FROM bill_upload bu inner join received_bills rb on bu.bill_no = rb.bill_no").getInt("nb_ex");
	}
	
	/**
	 * 
	 * @param billNo
	 * @param s: Service provider id
	 * @return
	 */
	public static Bill getBillByNo(String billNo, int s)
	{
		ResultSet rs = Database.retrieveData("SELECT * FROM received_bills where bill_no = '" + billNo + "' and fk_provider_id = '" + s + "'");
		String bno;
		try {
			bno = rs.getString("bill_no");
			String cno = rs.getString("customer_no");
			String bdt = rs.getDate("billing_date").toString();
			String ldt = rs.getDate("limit_date").toString();
			ServiceProvider sp = new ServiceProvider(rs.getInt("fk_provider_id"));
			Double bam = rs.getDouble("Amount");
			return new Bill(bno, cno, bdt, ldt, sp, bam);	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
			
	}
	

}
